﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.ComponentModel;

namespace QuanCafe.DataLayer
{
    public class SQLHelper
    {
        private SqlCommand cmd;
        private bool isOpen;
        private SqlConnection conn;
        private string strConnection = "";
        private SqlDataAdapter adpAdapter;
        private string ermess;
        private SqlTransaction trans;

        public SQLHelper()
        {
            strConnection = ConfigurationSettings.AppSettings["strConnection"].ToString();
            conn = new SqlConnection(strConnection);
            this.cmd = new SqlCommand();
            cmd.Connection = conn;
        }
        // mo ket noi
        public void Open()
        {
            if (isOpen)
            {
                return;
            }
            if (cmd.Connection.State != ConnectionState.Open)
            {
                if (conn.ConnectionString.Trim() == "")
                {
                    strConnection = ConfigurationSettings.AppSettings["strConnection"].ToString();
                    conn = new SqlConnection(strConnection);
                    //this.cmd = new SqlCommand();
                    cmd.Connection = conn;
                }
                cmd.Connection.Open();
                isOpen = true;
            }
        }
        //dong ket noi
        public void Close()
        {
            cmd.Connection.Close();
            conn.Close();
            isOpen = false;
        }
        // giai phong tai nguyen
        public void Dispose()
        {
            cmd.Connection.Dispose();
            cmd.Dispose();
            cmd.Connection.Close();
            conn.Close();
            isOpen = false;
        }
        //them tham so voi ten va gia tri
        public void AddParameter(string name, object value, SqlDbType type)
        {
            SqlParameter para = cmd.CreateParameter();
            para.SqlDbType = type;
            para.ParameterName = name;
            para.SqlValue = value;
            cmd.Parameters.Add(para);
        }
        //xoa tham so
        public void ClearAllParameters()
        {
            cmd.Parameters.Clear();
        }
        // thuc thi cau lenh SQL va kieu cau lenh
        public string ExecuteScalar(string commandText, CommandType type)
        {
            //cmd.ExecuteScalar();
            try
            {
                this.Open();
                trans = conn.BeginTransaction();
                //cmd = new SqlCommand();
                cmd.Transaction = trans;
                cmd.Connection = conn;
                cmd.CommandText = commandText;
                cmd.CommandType = type;
                cmd.Parameters.Add("@thongbao", SqlDbType.NVarChar, 100).Direction = ParameterDirection.InputOutput;
                cmd.ExecuteNonQuery();
                ermess = cmd.Parameters["@thongbao"].Value.ToString();
                trans.Commit();
                cmd.Parameters.Clear();
                this.Close();
                return ermess;
            }
            catch (Exception ex)
            {
                trans.Commit();
                cmd.Parameters.Clear();
                this.Close();
                throw ex;
            }
        }

        public DataTable ExecuteReader(string commandText, CommandType type)
        {
            try
            {
                this.Open();
                DataTable rsData = new DataTable();
                adpAdapter = new SqlDataAdapter();
                //SqlDataReader reader ;
                //trans = conn.BeginTransaction();

                //cmd.Transaction = trans;
                cmd.Connection = conn;
                cmd.CommandText = commandText;
                cmd.CommandType = type;
                //cmd.Parameters.Add("@thongbao", SqlDbType.NVarChar, 100).Direction = ParameterDirection.InputOutput;
                //reader = cmd.ExecuteReader();
                //ermess = cmd.Parameters["@thongbao"].Value.ToString();

                adpAdapter.SelectCommand = cmd;
                adpAdapter.Fill(rsData);
                cmd.Parameters.Clear();
                //trans.Commit();

                this.Close();
                return rsData;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public string ExecuteCommand(string commandText)
        {
            try
            {
                this.Open();
                cmd.CommandText = commandText;
                cmd.ExecuteNonQuery();

                cmd.Dispose();
                this.Close();
                this.Dispose();

                return "";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        // thuc thi cau lenh SQL va` tra~ ve 1 DataTable
        public DataTable GetDataTable(string commandText)
        {
            try
            {
                this.Open();
                DataTable rsData = new DataTable();
                adpAdapter = new SqlDataAdapter();

                cmd.CommandType = CommandType.Text;
                cmd.CommandText = commandText;
                cmd.CommandTimeout = 20000;

                adpAdapter.SelectCommand = cmd;
                adpAdapter.Fill(rsData);

                cmd.Dispose();
                this.Close();
                this.Dispose();
                adpAdapter.Dispose();
                return rsData;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        //thuc thi cau Select va` tra~ ve 1 dong`
        public DataRow GetDataRow(string pCommandText)
        {
            DataTable dt = new DataTable();
            DataRow mRow;
            dt = this.GetDataTable(pCommandText);

            mRow = null;
            if (dt != null)
            {
                if (dt.Rows.Count != 0)
                    mRow = dt.Rows[0];

                dt.Dispose();
            }
            this.Close();
            this.Dispose();
            return mRow;
        }
    }
}
